package com.erp.app.report.genericInquiry;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionServlet;

import com.erp.app.common.ProjectConstants;
import com.erp.app.report.genericInquiry.inquiryCriteria.beans.vo.InquiryCriteriaVO;
import com.erp.app.report.genericInquiry.inquiryDisplay.beans.vo.InquiryDisplayVO;
import com.erp.app.report.genericInquiry.inquiryMaster.beans.GenericInquiryMasterVO;
import com.erp.app.report.genericInquiry.inquiryOutput.beans.vo.InquiryOutputVO;
import com.erp.app.report.genericInquiry.inquirySp.beans.vo.InquirySpVO;
import com.erp.app.util.web.WebUtil;
import com.erp.framework.SimpleConstants;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;
import com.erp.framework.util.user.User;

public class GenericExcelGenerator extends GenericFileGenerator{

    public GenericExcelGenerator(GenericInquirySearchForm form) {
		super(form);
	}

	protected ByteArrayOutputStream genContent(
            ActionMapping mapping, 
            HttpServletRequest request, 
            HttpServletResponse response, 
            ActionServlet servlet, 
            ActionErrors errors) throws Exception {
		
		ByteArrayOutputStream baos = createReportByteArray(GenericInquiryConstants.JAVA_TYPE_EXCEL, false, mapping, request, response, servlet, errors);
    	
    	if (baos == null) {// use default
    		Logger.warn("Unable to locate java source, using default implementation instead.");
    		baos = getDefaultImpl(mapping, request, response, servlet, errors);
    	}
        
        return baos;
    }

    protected void preprocess(HttpServletRequest request, HttpServletResponse response, ActionServlet servlet, ActionErrors errors) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment; filename=\"" + CommonUtil.toValidFileName(form.getFilename()) + ".xls\"");
    }
    
    private ByteArrayOutputStream getDefaultImpl(
    		ActionMapping mapping,
    		HttpServletRequest request,
    		HttpServletResponse response,
    		ActionServlet servlet,
    		ActionErrors errors) throws IOException {
    	HttpSession session = request.getSession(false);
        GenericInquiryMasterVO vo = form.getGenericInquiryMasterVO();
        Collection spList = vo.getLocalChilds(InquirySpVO.class);
        Collection displayList = vo.getLocalChilds(InquiryDisplayVO.class);
        Collection critList = vo.getLocalChilds(InquiryCriteriaVO.class);
        Collection rsltList = form.getReportResultList();
        InquiryDisplayVO displayVO = null;
        InquiryOutputVO rsltVO = null;
        InquirySpVO spVO = (InquirySpVO)spList.iterator().next();
        InquiryCriteriaVO critVO = null;
        
        ByteArrayOutputStream baos = new ByteArrayOutputStream();        
        
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        wb.setSheetName(0, "Sheet1", HSSFWorkbook.ENCODING_UTF_16);
        HSSFCellStyle style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat(ProjectConstants.OUTPUT_DATE_FORMAT));
        HSSFRow row = null;
        int rowNum = 0;
        
        // set paper size to A4 2006-05-30
        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        //
        
        // Title
        row = sheet.createRow(rowNum++);
        HSSFCell cell = row.createCell((short)0);
        cell.setCellValue(spVO.getInquiryName());
        
        // Header - Date
        rowNum++;
        row = sheet.createRow(rowNum++);
//        Format formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss ");
	Format formatter = new SimpleDateFormat("yyyyMMdd HH:mm:ss ");
        String today = formatter.format(new Date());
        cell = row.createCell((short)0);
        cell.setCellValue("Date:");
        cell = row.createCell((short)1);
        cell.setCellValue(today);
        
        
        // Header - Company Code
        row = sheet.createRow(rowNum++);
        cell = row.createCell((short)0);
        cell.setCellValue("Company:");
        cell = row.createCell((short)1);
        cell.setCellValue(WebUtil.getAppropriateCompanyCode(session, null));
        
        // Header - User
	User editingUser = (User)session.getAttribute(ProjectConstants.SESSION_USER);
	String user_id = editingUser.getUserId();
	row = sheet.createRow(rowNum++);
        cell = row.createCell((short)0);
        cell.setCellValue("User:");
        cell = row.createCell((short)1);
        cell.setCellValue(user_id);
        
        // Search Criteria
        rowNum++;
        int counter = 0;
        for (Iterator critIt = critList.iterator(); critIt.hasNext();) {
            critVO = (InquiryCriteriaVO)critIt.next();
            row = sheet.createRow(rowNum++);
            if (critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_TEXT_FM_TO)
                    || critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_TEXT_FM_TO_OC)) {
                cell = row.createCell((short)0);
                cell.setCellValue("From " + critVO.getFieldName() + ":");
                cell = row.createCell((short)1);
                cell.setCellValue(form.getSrchFrom()[counter]);
                cell = row.createCell((short)2);
                cell.setCellValue("To " + critVO.getFieldName() + ":");
                cell = row.createCell((short)3);
                cell.setCellValue(form.getSrchTo()[counter]);
            } else if (critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_DROP_DOWN) ||
                        critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_DROP_DOWN_OC) ||
                        critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_MULTIPLE_DROP_DOWN) ||
                        critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_MULTIBOX) ||
                        critVO.getFieldType().equals(GenericInquiryConstants.FIELD_TYPE_MULTIBOX_HORIZONTAL)) {
                cell = row.createCell((short)0);
                cell.setCellValue(critVO.getFieldName() + ":");
                cell = row.createCell((short)1);
                cell.setCellValue(form.getSrchFromLabel()[counter]);
            } else {
                cell = row.createCell((short)0);
                cell.setCellValue(critVO.getFieldName() + ":");
                cell = row.createCell((short)1);
                cell.setCellValue(form.getSrchFrom()[counter]);
            }
            counter++;
        }
        
        rowNum+=2;
        row = sheet.createRow(rowNum);
        counter = 0;
        for (Iterator displayIt = displayList.iterator(); displayIt.hasNext();) {
            displayVO = (InquiryDisplayVO)displayIt.next();
            cell = row.createCell((short)counter);
            cell.setCellValue(displayVO.getDispName());
            counter++;
        }
       
        rowNum++;

//      Header & Content
        String fieldNature = "";
        String fieldValue = "";
        for (Iterator rsltIt = rsltList.iterator(); rsltIt.hasNext();) {
            rsltVO = (InquiryOutputVO)rsltIt.next();
            counter = 0;
            row = sheet.createRow(rowNum++);
            for (Iterator displayIt = displayList.iterator(); displayIt.hasNext();) {
                displayVO = (InquiryDisplayVO)displayIt.next();
                fieldNature = CommonUtil.nullToString(displayVO.getFieldNature());
                fieldValue = CommonUtil.nullToString(rsltVO.getField(counter));
                String dispCode = displayVO.getDispCode(); 
                cell = row.createCell((short)counter);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                
                if (fieldNature.equals(GenericInquiryConstants.DATA_TYPE_NUMERIC)) {
                    double dbValue = 0;
                    try {
                        dbValue = Double.parseDouble(CommonUtil.stringRpl(fieldValue, ",", ""));
                        cell.setCellValue(dbValue);
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    } catch (NumberFormatException e) {
                        cell.setCellValue("");
                        cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                    }
                } else if (fieldNature.equals(GenericInquiryConstants.DATA_TYPE_DATE)) {
                	if (!CommonUtil.isEmpty(fieldValue)){
                		try {
                			Date date = CommonUtil.stringToDate(fieldValue, SimpleConstants.RPT_DATE_FORMAT, GenericInquiryConstants.getDefaultLocale());
                			cell.setCellValue(date);
                		} catch (java.text.ParseException e) {
	                            cell.setCellValue("");
	                            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
	                        } 
	                		
	                        cell.setCellStyle(style);
                	} else {
                		cell.setCellValue("");
                	}
                    //cell.setCellValue(tmpDate);

                } else
                    cell.setCellValue(fieldValue);
                counter++;
            }
        }
        
        wb.write(baos);
        return baos;
    }
}
